import pymysql
from sshtunnel import SSHTunnelForwarder

from config import ssh_host, ssh_port, ssh_username, ssh_password, db_host, db_port, db_password, db_username, db_name

config_data = {
    'host': '127.0.0.1',
    'user': db_username,
    'password': db_password,
    'charset': "utf8",
}
database_list = db_name


class DBUtil(object):
    # 属性
    server = None
    conn = None

    @classmethod
    def __get_conn(cls):
        connections = []
        # 判断 conn 是否为空, 如果是,再创建
        if cls.conn is None:
            # 搭建开启SSH服务
            cls.server = SSHTunnelForwarder(ssh_address_or_host=(ssh_host, ssh_port), ssh_username=ssh_username,
                                            ssh_password=ssh_password,
                                            remote_bind_address=(db_host, db_port))
            cls.server.start()

            database_list_data = database_list.copy()
            for database_name in database_list_data:
                # 防止原始字典被修改
                new_data = config_data.copy()
                # 给新的字典添加字段并赋值
                new_data['database'] = database_name
                # 连接4个数据库
                cls.conn = pymysql.connect(port=cls.server.local_bind_port, **new_data)
                # 存储conn信息对象
                connections.append(cls.conn)
            # 返回，conn信息对象列表
            return connections

    # 关闭连接方法
    @classmethod
    def __close_conn(cls):
        if cls.conn is not None:
            # 关闭数据库连接
            cls.conn.close()
            # 关闭SSH服务
            cls.server.close()
            cls.conn = None

    # mysql查询
    @classmethod
    def select_one(cls, sql):
        cursor = None
        res = None
        try:
            # 建立连接
            conn_list = cls.__get_conn()
            for cls.conn in conn_list:
                # 获取游标
                cursor = cls.conn.cursor()
                # 执行 sql 语句
                cursor.execute(sql)
                # 获取一条数据
                res = cursor.fetchone()

        except Exception as err:
            print("查询语句执行出错：", str(err))
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            cls.__close_conn()
            # 返回结果
            return res

    # mysql增删改
    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            # 建立连接
            conn_list = cls.__get_conn()
            for cls.conn in conn_list:
                # 获取游标
                cursor = cls.conn.cursor()
                # 执行 sql 语句
                cursor.execute(sql)
                # 查看 sql 执行,影响多少行
                print("影响的数据库对象与行数：", cls.conn, cls.conn.affected_rows())
                # 提交事务
                cls.conn.commit()

        except Exception as err:
            print("增删改语句执行出错：", str(err))
            # 回滚事务
            cls.conn.rollback()
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            cls.__close_conn()


if __name__ == '__main__':
    # res1 = DBUtil.select_one("select tenant_id, tenant_name from sys_tenant where  tenant_name = 'JMET'")
    # print(res1)
    # DBUtil.uid_db(
    #     "INSERT INTO sys_user(`user_id`, `dept_id`, `user_name`, `nick_name`, `user_type`, `email`, `phonenumber`, `sex`, `avatar`, `password`, `status`, `del_flag`, `login_ip`, `login_date`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `tenant_id`) VALUES (10000, 100, 'code_test', 'code_test', '00', '', '', '0', '', '123123123123123123123', '0', '0', '', NULL, 'admin', '2023-06-21 17:29:36', '', NULL, NULL, 999999);")
    # DBUtil.uid_db("delete from sys_user where user_id = '10000';")
    # DBUtil.uid_db("delete from sys_tenant where tenant_name = 'jm004'")
    res1 = DBUtil.select_one("select user_id, del_flag from sys_user where user_name='chen'")
    print(res1)
